CREATE TABLE users (
    user_id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(255),
    phone_number VARCHAR(15),
    email VARCHAR(100),
    date_joined DATE
);

CREATE TABLE courses (
    course_id VARCHAR(50) PRIMARY KEY,
    subject VARCHAR(100),
    description TEXT,
    start_date DATE,
    end_date DATE,
    instructor VARCHAR(100)
);

CREATE TABLE transactions (
    transaction_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50),
    course_id VARCHAR(50),
    transaction_date DATE,
    amount DECIMAL(10, 2),
    status VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO users (user_id, name, address, phone_number, email, date_joined) VALUES 
('U1', 'Rajkumar', '16 st james garden', '7881143522', 'rajkumar1@gmail.com', '2023-11-10'),
('U2', 'Rajashekar', '19 kings road', '7881142511', 'rajashekar2@gmail.com', '2023-06-09'),
('U3', 'Rajesh', '23 st helens', '7955436879', 'rajesh3@gmail.com', '2024-09-20'),
('U4', 'Raghupathi', '32 henreitt Street', '7945855432', 'raghupathi4@gmail.com', '2024-01-22'),
('U5', 'Nikhilvarma', '58 catherine St', '7785542210', 'nikhilvarma5@gmail.com', '2024-05-19'),
('U6', 'Kamalakar', '18 edwards road', '7668943321', 'kamalakar6@email.com', '2023-07-13'),
('U7', 'Vishnu', '45 kings way', '7605311832', 'vishnu7@gmail.com', '2024-10-15'),
('U8', 'Varma', '49 mumbles', '7552110943', 'varma8@gmail.com', '2024-02-26'),
('U9', 'Prathamesh', '65 james street', '7552199087', 'prathaesh9@gmail.com', '2023-04-20'),
('U10', 'Ajay', '73 morriston', '7092884315', 'ajay10@gmail.com', '2024-03-25');

INSERT INTO courses (course_id, subject, description, start_date, end_date, instructor) VALUES 
('C01', 'Math 101', 'Introduction to Mathematics', '2024-01-10', '2024-05-20', 'Dr. John Smith'),
('C02', 'Physics 101', 'Basic Physics Concepts', '2024-02-01', '2024-06-30', 'Dr. Jane Doe'),
('C03', 'Chemistry 101', 'Fundamentals of Chemistry', '2024-03-01', '2024-07-15', 'Dr. Robert Brown'),
('C04', 'Biology 101', 'Introduction to Biology', '2024-04-01', '2024-08-15', 'Dr. Alice Green'),
('C05', 'Computer Science 101', 'Introduction to Computer Science', '2024-05-01', '2024-09-10', 'Dr. Samuel White'),
('C06', 'Economics 101', 'Introduction to Economics', '2024-06-01', '2024-10-30', 'Dr. Linda Harris'),
('C07', 'History 101', 'World History', '2024-07-01', '2024-11-15', 'Dr. Mark Taylor'),
('C08', 'English 101', 'English Literature', '2024-08-15', '2024-12-25', 'Dr. Emily Clark'),
('C09', 'Political Science 101', 'Introduction to Political Science', '2024-05-10', '2024-09-15', 'Dr. Linda Martin'),
('C10', 'Sociology 101', 'Introduction to Sociology', '2024-06-15', '2024-11-05', 'Dr. Andrew Lee');

INSERT INTO transactions (transaction_id, user_id, course_id, transaction_date, amount, status) VALUES 
('T001', 'U1', 'C01', '2024-01-15', 1000, 'completed'),
('T002', 'U2', 'C02', '2024-02-10', 1200, 'completed'),
('T003', 'U3', 'C03', '2024-03-05', 1100, 'pending'),
('T004', 'U4', 'C04', '2024-04-01', 1300, 'completed'),
('T005', 'U5', 'C05', '2024-05-10', 1500, 'failed'),
('T006', 'U6', 'C06', '2024-06-05', 800, 'completed'),
('T007', 'U7', 'C07', '2024-07-05', 950, 'completed'),
('T008', 'U8', 'C08', '2024-08-20', 1100, 'pending'),
('T009', 'U9', 'C09', '2024-05-12', 1050, 'completed'),
('T010', 'U10', 'C10', '2024-06-20', 1150, 'completed');

SELECT * FROM Users;
SELECT * FROM Courses;
SELECT * FROM Transactions;

ALTER TABLE users
ADD CONSTRAINT chk_phone_number CHECK (phone_number ~ '^\d{10}$');

ALTER TABLE users
ADD CONSTRAINT chk_email_format CHECK (email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

ALTER TABLE users
ADD CONSTRAINT chk_date_joined CHECK (date_joined <= CURRENT_DATE);
\d users

